package com.googlecode.bip.dao.mysql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import javax.naming.NamingException;
import org.apache.log4j.Logger;
import com.googlecode.bip.dao.UserIgnoreQuestionDAO;
import com.googlecode.bip.dao.utils.DBHelper;
import com.googlecode.bip.dao.utils.Listable;
import com.googlecode.bip.entity.IUserIgnoreQuestion;
import cx.ath.choisnet.sql.ConnectionQuery;

/**
 *
 * @author Claude CHOISNET
 */
public class MySQLUserIgnoreQuestionDAO extends UserIgnoreQuestionDAO
{
    private static final long serialVersionUID = 1L;
    private static final Logger logger = Logger.getLogger( MySQLUserIgnoreQuestionDAO.class );

    @Override //Listable<IQuestion>
    public List<IUserIgnoreQuestion> getList( String sqlQuery ) throws SQLException, NamingException
    {
        Connection connection = MySQLDAOFactory.getConnection();

        try {
            //return DBHelper.getList( this, getDataSource(), sqlQuery );
            return DBHelper.getList( this, connection, sqlQuery );
            }
        finally {
            MySQLDAOFactory.returnConnectionToPool( connection );
            }
    }

    /**
     *
     * @param iduser
     * @return
     * @throws SQLException
     * @throws NamingException
     */
    public List<IUserIgnoreQuestion> findUserIgnoreQuestion( int iduser )
        throws SQLException, NamingException
    {
        final String sql = "SELECT * FROM `userignorequestions` WHERE `iduser`="
            + iduser
            + ";";

        return getList( sql );
    }

    /**
     *
     * @param iduser
     * @return
     * @throws SQLException
     * @throws NamingException
     */
    public List<Integer> findIgnoreQuestionList( int iduser )
        throws SQLException, NamingException
     {
        final String sql = "SELECT * FROM `userignorequestions` WHERE `iduser`="
            + iduser
            + ";";
        Listable<Integer> listBuilder = new Listable<Integer>()
        {
         @Override
         public List<Integer> getList( String sql ) throws SQLException, NamingException
         {
             Connection connection = MySQLDAOFactory.getConnection();

             try {
                 return DBHelper.getList( this, connection, sql );
                 }
             finally {
                 MySQLDAOFactory.returnConnectionToPool( connection );
                 }
         }
         @Override
         public Integer newInstance( ResultSet resultSet ) throws SQLException
         {
             return resultSet.getInt( "idquestion" );
         }
        };

        return listBuilder.getList( sql );
     }

    /**
     * Insert a new IUserIgnoreQuestion (must not exist)
     * @param iquestion
     * @return
     * @throws NamingException
     * @throws SQLException
     */
    public boolean insertUserIgnoreQuestion( final IUserIgnoreQuestion iuiq )
        throws NamingException, SQLException
    {
        final String sql = "INSERT INTO `userignorequestions` (`iduser`, `idquestion`) VALUES( "
            + iuiq.getIdUser()
            + ", "
            + iuiq.getIdQuestion()
            + " );";

        if( logger.isDebugEnabled() ) {
            logger.debug( "insertUserIgnoreQuestion: " + sql );
            }

        Connection connection = MySQLDAOFactory.getConnection();
        final ConnectionQuery query = new ConnectionQuery( connection );

        try {
            int r = query.doUpdate( sql );

            return r == 1;
            }
        finally {
            query.quietClose();
            MySQLDAOFactory.returnConnectionToPool( connection );
            }
    }

    /**
     *
     * @param idquestion
     * @return
     * @throws NamingException
     * @throws SQLException
     */
    public boolean deleteUserIgnoreQuestion( final IUserIgnoreQuestion iuiq )
        throws NamingException, SQLException
    {
         final String sql = "DELETE FROM `userignorequestions` WHERE `iduser`="
            + iuiq.getIdUser()
            + " AND `idquestion`="
            + iuiq.getIdQuestion()
            + ";";

        if( logger.isDebugEnabled() ) {
            logger.debug( "deleteUserIgnoreQuestion 1: sql = " + sql );
            }

        Connection connection = MySQLDAOFactory.getConnection();
        final ConnectionQuery query = new ConnectionQuery( connection );

        try {
            int x = query.doUpdate( sql );

            logger.debug( "deleteUserIgnoreQuestion 1: r = " + x );

            return x == 1;
            }
        catch( SQLException e ) {
            logger.fatal( "deleteUserIgnoreQuestion: sql = " + sql, e );
            throw e;
            }
        finally {
            query.quietClose();
            MySQLDAOFactory.returnConnectionToPool( connection );
            }
    }

    /**
     *
     * @param iduser
     * @param idquestionList
     * @throws SQLException
     * @throws NamingException
     */
    public void deleteUserIgnoreQuestion(
            final int iduser,
            final List<Integer> idquestionList
            )
        throws SQLException, NamingException
    {
        final String sql = "DELETE FROM `userignorequestions` WHERE `iduser`="
            + iduser
            + " AND `idquestion`=?;";

        if( logger.isDebugEnabled() ) {
            logger.debug( "deleteUserIgnoreQuestion 2: sql = " + sql );
            }

        Connection connection = MySQLDAOFactory.getConnection();

         try {
             PreparedStatement ps = null;

             try {
                 ps = connection.prepareStatement( sql );

                 for( int idquestion : idquestionList ) {
                     ps.setInt( 1, idquestion );
                     ps.executeUpdate();
                     }
                 }
             finally {
                 if( ps != null ) {
                     ps.close();
                     }
                 }
             }
         finally {
             MySQLDAOFactory.returnConnectionToPool( connection );
             }
    }

}
